import mysql.connector
from mysql.connector import Error
from datetime import datetime, timedelta
import pandas as pd
import matplotlib.pyplot as plt
import numpy as np


# 工单编号：大数据-八维保险数据挖掘-03-财产保险业务看板相关

class InsuranceKPICalculator:
    """财产保险业务指标计算器"""

    def __init__(self, host, database, user, password):
        """初始化数据库连接"""
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        self.connection = None

    def connect(self):
        """建立数据库连接"""
        try:
            self.connection = mysql.connector.connect(
                host=self.host,
                database=self.database,
                user=self.user,
                password=self.password
            )
            if self.connection.is_connected():
                db_info = self.connection.get_server_info()
                print(f"成功连接到MySQL服务器版本: {db_info}")
                return True
        except Error as e:
            print(f"连接数据库时发生错误: {e}")
            return False

    def disconnect(self):
        """关闭数据库连接"""
        if self.connection.is_connected():
            self.connection.close()
            print("数据库连接已关闭")

    def get_date_ranges(self, period_type, end_date=None, count=12):
        """
        生成指定类型的日期范围

        参数:
            period_type: 周期类型，可选值: 'daily', 'weekly', 'monthly', 'yearly'
            end_date: 结束日期，默认为当前日期
            count: 生成的周期数量

        返回:
            日期范围列表，每个元素是(start_date, end_date)元组
        """
        if end_date is None:
            end_date = datetime.now()

        date_ranges = []

        for i in range(count):
            if period_type == 'daily':
                current_end = end_date - timedelta(days=i)
                current_start = current_end
            elif period_type == 'weekly':
                current_end = end_date - timedelta(weeks=i)
                current_start = current_end - timedelta(days=6)
            elif period_type == 'monthly':
                # 计算月份的最后一天
                year = current_end.year
                month = current_end.month - i
                if month <= 0:
                    month += 12
                    year -= 1
                current_end = datetime(year, month, 1) + timedelta(days=32)
                current_end = current_end.replace(day=1) - timedelta(days=1)
                current_start = datetime(year, month, 1)
            elif period_type == 'yearly':
                year = end_date.year - i
                current_start = datetime(year, 1, 1)
                current_end = datetime(year, 12, 31)
            else:
                raise ValueError("不支持的周期类型，必须是 'daily', 'weekly', 'monthly' 或 'yearly'")

            date_ranges.append((current_start.strftime('%Y-%m-%d'),
                                current_end.strftime('%Y-%m-%d')))

        # 反转列表，使日期从早到晚排列
        return date_ranges[::-1]

    def calculate_agent_dropout_rate(self, region, start_date, end_date):
        """
        计算营销员脱落率

        公式: 营销员脱落率 = 报告期离职营销员数量 ÷[期初营销员数量 +（报告期新增营销员数量 - 报告期离职营销员数量）]×100%
        """
        try:
            cursor = self.connection.cursor(dictionary=True)

            query = """
            SELECT 
                region,
                (leave_count / NULLIF(initial_count + new_count - leave_count, 0) * 100) AS dropout_rate
            FROM (
                SELECT 
                    region,
                    -- 期初营销员数量
                    SUM(CASE WHEN join_date < %s AND (leave_date IS NULL OR leave_date >= %s) THEN 1 ELSE 0 END) AS initial_count,
                    -- 报告期新增营销员数量
                    SUM(CASE WHEN join_date BETWEEN %s AND %s THEN 1 ELSE 0 END) AS new_count,
                    -- 报告期离职营销员数量
                    SUM(CASE WHEN leave_date BETWEEN %s AND %s THEN 1 ELSE 0 END) AS leave_count
                FROM agents
                WHERE region = %s
                GROUP BY region
            ) AS agent_stats
            """

            cursor.execute(query, (start_date, start_date, start_date, end_date,
                                   start_date, end_date, region))
            result = cursor.fetchone()

            return result['dropout_rate'] if result and result['dropout_rate'] is not None else 0

        except Error as e:
            print(f"计算营销员脱落率时发生错误: {e}")
            return None

    def calculate_new_policy_ratio(self, region, start_date, end_date):
        """
        计算新单保额与新单保费比

        公式: 新单保额与新单保费比 = 新单保险金额 ÷ 新单原保费收入
        """
        try:
            cursor = self.connection.cursor(dictionary=True)

            query = """
            SELECT 
                region,
                SUM(amount) / NULLIF(SUM(premium), 0) AS amount_to_premium_ratio
            FROM policies
            WHERE 
                issue_date BETWEEN %s AND %s
                AND is_new = TRUE
                AND region = %s
            GROUP BY region
            """

            cursor.execute(query, (start_date, end_date, region))
            result = cursor.fetchone()

            return result['amount_to_premium_ratio'] if result and result['amount_to_premium_ratio'] is not None else 0

        except Error as e:
            print(f"计算新单保额与新单保费比时发生错误: {e}")
            return None

    def calculate_insurance_type_ratio(self, region, start_date, end_date):
        """
        计算分险种保费占比

        公式: 分险种保费占比 = 各险种原/分保费收入 ÷ 各险种原/分保费收入之和 ×100%
        """
        try:
            cursor = self.connection.cursor(dictionary=True)

            query = """
            SELECT 
                t.type_name,
                p.region,
                (SUM(p.premium) / total.total_premium * 100) AS premium_ratio
            FROM policies p
            JOIN insurance_types t ON p.type_id = t.type_id
            JOIN (
                SELECT 
                    region,
                    SUM(premium) AS total_premium
                FROM policies
                WHERE issue_date BETWEEN %s AND %s
                    AND region = %s
                GROUP BY region
            ) AS total ON p.region = total.region
            WHERE 
                p.issue_date BETWEEN %s AND %s
                AND p.region = %s
            GROUP BY t.type_name, p.region, total.total_premium
            ORDER BY premium_ratio DESC
            """

            cursor.execute(query, (start_date, end_date, region,
                                   start_date, end_date, region))
            results = cursor.fetchall()

            return {item['type_name']: item['premium_ratio'] for item in results}

        except Error as e:
            print(f"计算分险种保费占比时发生错误: {e}")
            return None

    def calculate_channel_ratio(self, region, start_date, end_date):
        """
        计算分渠道保费占比

        公式: 分渠道保费占比 = 各渠道原/分保费收入 ÷ 各渠道原/分保费收入之和 ×100%
        """
        try:
            cursor = self.connection.cursor(dictionary=True)

            query = """
            SELECT 
                c.channel_name,
                p.region,
                (SUM(p.premium) / total.total_premium * 100) AS channel_premium_ratio
            FROM policies p
            JOIN channels c ON p.channel_id = c.channel_id
            JOIN (
                SELECT 
                    region,
                    SUM(premium) AS total_premium
                FROM policies
                WHERE issue_date BETWEEN %s AND %s
                    AND region = %s
                GROUP BY region
            ) AS total ON p.region = total.region
            WHERE 
                p.issue_date BETWEEN %s AND %s
                AND p.region = %s
            GROUP BY c.channel_name, p.region, total.total_premium
            ORDER BY channel_premium_ratio DESC
            """

            cursor.execute(query, (start_date, end_date, region,
                                   start_date, end_date, region))
            results = cursor.fetchall()

            return {item['channel_name']: item['channel_premium_ratio'] for item in results}

        except Error as e:
            print(f"计算分渠道保费占比时发生错误: {e}")
            return None

    def calculate_all_kpis(self, region, period_type='weekly', count=7):
        """
        计算指定区域的所有KPI指标，按指定周期统计

        参数:
            region: 区域名称
            period_type: 周期类型: 'daily', 'weekly', 'monthly', 'yearly'
            count: 统计的周期数量

        返回:
            包含所有KPI指标的字典
        """
        # 获取日期范围
        date_ranges = self.get_date_ranges(period_type, count=count)

        # 初始化结果字典
        results = {
            'periods': [f"{start} 至 {end}" for start, end in date_ranges],
            'agent_dropout_rate': [],
            'new_policy_ratio': [],
            'insurance_type_ratio': [],
            'channel_ratio': []
        }

        # 为每个周期计算KPI
        for start_date, end_date in date_ranges:
            print(f"计算 {start_date} 至 {end_date} 的指标...")

            # 计算营销员脱落率
            dropout_rate = self.calculate_agent_dropout_rate(region, start_date, end_date)
            results['agent_dropout_rate'].append(round(dropout_rate, 2) if dropout_rate else None)

            # 计算新单保额与新单保费比
            new_policy_ratio = self.calculate_new_policy_ratio(region, start_date, end_date)
            results['new_policy_ratio'].append(round(new_policy_ratio, 2) if new_policy_ratio else None)

            # 计算分险种保费占比
            insurance_type_ratio = self.calculate_insurance_type_ratio(region, start_date, end_date)
            results['insurance_type_ratio'].append(insurance_type_ratio)

            # 计算分渠道保费占比
            channel_ratio = self.calculate_channel_ratio(region, start_date, end_date)
            results['channel_ratio'].append(channel_ratio)

        return results

    def visualize_kpis(self, kpi_data, region):
        """可视化KPI数据"""
        # 设置中文显示
        plt.rcParams["font.family"] = ["SimHei", "WenQuanYi Micro Hei", "Heiti TC"]

        # 创建一个包含多个子图的图表
        fig, axes = plt.subplots(2, 1, figsize=(12, 16))
        fig.suptitle(f'{region} 财产保险业务指标分析', fontsize=16)

        # 1. 营销员脱落率趋势图
        ax1 = axes[0]
        ax1.plot(kpi_data['periods'], kpi_data['agent_dropout_rate'], 'o-', color='red')
        ax1.set_title('营销员脱落率趋势 (%)')
        ax1.set_xticklabels(kpi_data['periods'], rotation=45, ha='right')
        ax1.grid(True, linestyle='--', alpha=0.7)

        # 2. 新单保额与新单保费比趋势图
        ax2 = axes[1]
        ax2.plot(kpi_data['periods'], kpi_data['new_policy_ratio'], 'o-', color='blue')
        ax2.set_title('新单保额与新单保费比趋势')
        ax2.set_xticklabels(kpi_data['periods'], rotation=45, ha='right')
        ax2.grid(True, linestyle='--', alpha=0.7)

        plt.tight_layout(rect=[0, 0, 1, 0.96])  # 调整布局，为标题留出空间
        plt.show()

        # 3. 最后一期的分险种保费占比饼图
        last_insurance_ratios = kpi_data['insurance_type_ratio'][-1]
        if last_insurance_ratios:
            plt.figure(figsize=(10, 8))
            labels = list(last_insurance_ratios.keys())
            sizes = list(last_insurance_ratios.values())
            plt.pie(sizes, labels=labels, autopct='%1.1f%%', startangle=90)
            plt.axis('equal')
            plt.title(f'{kpi_data["periods"][-1]} 分险种保费占比')
            plt.show()

        # 4. 最后一期的分渠道保费占比饼图
        last_channel_ratios = kpi_data['channel_ratio'][-1]
        if last_channel_ratios:
            plt.figure(figsize=(10, 8))
            labels = list(last_channel_ratios.keys())
            sizes = list(last_channel_ratios.values())
            plt.pie(sizes, labels=labels, autopct='%1.1f%%', startangle=90)
            plt.axis('equal')
            plt.title(f'{kpi_data["periods"][-1]} 分渠道保费占比')
            plt.show()


# 主函数
def main():
    # 数据库连接参数（请根据实际情况修改）
    db_config = {
        'host': 'localhost',
        'database': 'insurance_db',
        'user': 'root',
        'password': 'password'
    }

    # 创建KPI计算器实例
    kpi_calculator = InsuranceKPICalculator(**db_config)

    # 连接数据库
    if kpi_calculator.connect():
        try:
            # 计算华东区最近7周的指标
            region = '华东区'
            kpi_data_weekly = kpi_calculator.calculate_all_kpis(region, 'weekly', 7)
            print(f"\n{region} 最近7周业务指标计算完成")

            # 计算华东区最近12个月的指标
            kpi_data_monthly = kpi_calculator.calculate_all_kpis(region, 'monthly', 12)
            print(f"\n{region} 最近12个月业务指标计算完成")

            # 计算华东区最近3年的指标
            kpi_data_yearly = kpi_calculator.calculate_all_kpis(region, 'yearly', 3)
            print(f"\n{region} 最近3年业务指标计算完成")

            # 可视化周度指标
            kpi_calculator.visualize_kpis(kpi_data_weekly, region)

            # 将结果保存为CSV
            weekly_df = pd.DataFrame({
                '周期': kpi_data_weekly['periods'],
                '营销员脱落率(%)': kpi_data_weekly['agent_dropout_rate'],
                '新单保额与新单保费比': kpi_data_weekly['new_policy_ratio']
            })
            weekly_df.to_csv(f'{region}_weekly_kpi.csv', index=False, encoding='utf-8-sig')
            print(f"\n周度指标已保存至 {region}_weekly_kpi.csv")

        finally:
            # 断开数据库连接
            kpi_calculator.disconnect()


if __name__ == "__main__":
    main()
